package excelrept

import (
	"bytes"
	"fmt"
	"os"
	"os/exec"
	"strconv"
	"strings"

	"github.com/xuri/excelize/v2"
	"gorm.io/gorm"
	"shqsoft.cn/pas3ui/base"
)

//Import 导入excel
func Import(reptName string, params map[string]string, fileName string) (err error) {
	var workPath string
	if workPath, err = base.WorkDir(); err != nil {
		return fmt.Errorf("获取执行路径出错！%s", err)
	}
	filePath := workPath + "/upload_file/" + fileName
	convExePath := workPath + "/" + "excelConvert.exe"
	var xlsxPath string
	if strings.HasSuffix(fileName, ".xls") {
		xlsxPath = filePath + "x"
		os.Remove(xlsxPath)
		cmd := exec.Command(convExePath, filePath, xlsxPath)
		_, err = cmd.CombinedOutput()
		if err != nil {
			return
		}
	} else if strings.HasSuffix(fileName, ".xlsx") {
		xlsxPath = filePath
	} else {
		return fmt.Errorf("导入文件%s有误，只能导入excel文件", fileName)
	}
	xlsx, err := NewXlsxHelper(xlsxPath)
	if err != nil {
		return err
	}
	conf, err := getConfig(reptName)
	if err != nil {
		return err
	}
	tlen := len(conf.Tables)
	if tlen == 0 {
		return fmt.Errorf("报表%s没有配置tables", reptName)
	}
	tx := base.CHDB.Begin()
	offset := 0
	prevSheet := ""
	for _, t := range conf.Tables {
		if t.Sheet != prevSheet {
			offset = 0
		}
		offset, err = importTable(&t, xlsx, params, offset, tx)
		if err != nil {
			tx.Rollback()
			return
		}
		prevSheet = t.Sheet
	}
	tx.Commit()
	err = UpdateChuShi(reptName, params)
	if err != nil {
		return err
	}
	return nil
}

func importTable(tConf *Table, xlsx *XlsxHelper, params map[string]string, startOffset int, tx *gorm.DB) (endOffset int, err error) {
	err = delTable(tConf, params, tx)
	if err != nil {
		return
	}
	sqlStr, vals, err := getInsertSQL(tConf, params)
	if err != nil {
		return 0, err
	}
	rs := strings.Split(tConf.DataRange, ":")
	if len(rs) != 2 {
		err = fmt.Errorf("非法的单元格范围表达式%s", rs)
		return
	}
	var scol, srow, ecol, erow int
	scol, srow, err = parseAxis(rs[0])
	if err != nil {
		return
	}
	ecol, erow, err = parseAxis(rs[1])
	if err != nil {
		return
	}
	srow += startOffset
	erow += startOffset
	var cn, colName string
	var ok bool
	var cVal interface{}
	var cValStr string
	var cell string
	var isIn bool
	listSheet := xlsx.file.GetSheetList()
	sheetName := tConf.Sheet
	var isFind bool
	for _, s := range listSheet {
		if sheetName == s {
			isFind = true
		}
	}
	if !isFind {
		sheetName = listSheet[0]
	}
	var insVals []interface{}
	_, hasRowno := tConf.Columns[tConf.RowNoCol]
	vlen := len(vals) + len(tConf.Columns)
	if !hasRowno {
		vlen++
	}
	if len(tConf.Columns) > 0 {
		rowno := 1
		for i := srow; i <= erow; i++ {
			insVals = make([]interface{}, 0, vlen)
			insVals = append(insVals, vals...)
			if !hasRowno {
				insVals = append(insVals, rowno)
				rowno++
			}
			for j := scol; j <= ecol; j++ {
				cn, err = excelize.ColumnNumberToName(j)
				if err != nil {
					return
				}
				colName, ok = tConf.Columns[cn]
				if !ok {
					continue
				}
				cell = cn + strconv.Itoa(i)
				isIn = false
				for _, rg := range tConf.UnusedCells {
					if inRange(rg, cell) {
						isIn = true
						break
					}
				}
				if isIn {
					cVal = nil
				} else {
					cValStr, err = xlsx.file.GetCellValue(sheetName, cell)
					if err != nil {
						return
					}
					cValStr = strings.TrimSpace(cValStr)
					if colName == tConf.RowNoCol {
						if cValStr == "合计" {
							cValStr = "999999"
						}
					}
					cVal, err = Convert(tConf.Name, colName, cValStr)
					if err != nil {
						err = fmt.Errorf("表单%s单元格%s错误,%s", sheetName, cell, err.Error())
						return
					}
				}
				insVals = append(insVals, cVal)
			}
			err = tx.Exec(sqlStr, insVals...).Error
			if err != nil {
				return
			}
		}
	} else {
		insVals = make([]interface{}, 0, len(vals)+len(tConf.Cells))
		insVals = append(insVals, vals...)
		for i := scol; i <= ecol; i++ {
			cn, err = excelize.ColumnNumberToName(i)
			if err != nil {
				return
			}
			for j := srow; j <= erow; j++ {
				cell = cn + strconv.Itoa(j)
				colName, ok = tConf.Cells[cell]
				if !ok {
					continue
				}
				cValStr, err = xlsx.file.GetCellValue(sheetName, cell)
				if err != nil {
					return
				}
				cVal, err = Convert(tConf.Name, colName, cValStr)
				if err != nil {
					err = fmt.Errorf("表单%s单元格%s错误,%s", sheetName, cell, err.Error())
					return
				}
				insVals = append(insVals, cVal)
			}
		}
		err = tx.Exec(sqlStr, insVals...).Error
		if err != nil {
			return
		}
	}
	return
}
func delTable(tConf *Table, params map[string]string, tx *gorm.DB) (err error) {
	var buff bytes.Buffer
	buff.WriteString("delete from ")
	buff.WriteString(tConf.Name)
	plen := len(params)
	vals := make([]interface{}, plen)
	dbCols, err := getDBColumns(tConf.Name)
	if err != nil {
		return err
	}
	if plen > 0 {
		buff.WriteString(" where ")
		var convF ConvertFunc
		i := 0
		for k, v := range params {
			convF = nil
			for _, col := range dbCols {
				if strings.EqualFold(col.Name, k) {
					convF = col.convertFunc
					break
				}
			}
			if convF == nil {
				return fmt.Errorf("参数名%s在表%s中没有定义的字段", k, tConf.Name)
			}
			buff.WriteString(k)
			buff.WriteString("=?")
			if i < plen-1 {
				buff.WriteString(" and ")
			}
			vals[i], err = convF(v)
			if err != nil {
				return err
			}
			i++
		}
	}
	delSQL := buff.String()
	return tx.Exec(delSQL, vals...).Error
}
func getInsertSQL(tConf *Table, params map[string]string) (sqlStr string, vals []interface{}, err error) {
	var buff bytes.Buffer
	buff.WriteString("insert into ")
	buff.WriteString(tConf.Name)
	buff.WriteString("(")
	dbCols, err := getDBColumns(tConf.Name)
	if err != nil {
		return
	}
	plen := len(params)
	vals = make([]interface{}, plen)
	if plen > 0 {
		var convF ConvertFunc
		i := 0
		for k, v := range params {
			convF = nil
			for _, col := range dbCols {
				if strings.EqualFold(col.Name, k) {
					convF = col.convertFunc
					break
				}
			}
			if convF == nil {
				err = fmt.Errorf("参数名%s在表%s中没有定义的字段", k, tConf.Name)
				return
			}
			buff.WriteString(k)
			buff.WriteString(",")
			vals[i], err = convF(v)
			if err != nil {
				return
			}
			i++
		}
	}
	rs := strings.Split(tConf.DataRange, ":")
	if len(rs) != 2 {
		err = fmt.Errorf("非法的单元格范围表达式%s", rs)
		return
	}
	scol := 1
	var srow, ecol, erow int
	_, srow, err = parseAxis(rs[0])
	if err != nil {
		return
	}
	ecol, erow, err = parseAxis(rs[1])
	if err != nil {
		return
	}
	var colName, cn string
	var ok bool
	var aixs string
	_, hasRowno := tConf.Columns[tConf.RowNoCol]
	if !hasRowno {
		buff.WriteString(tConf.RowNoCol)
		buff.WriteString(",")
	}
	for i := scol; i <= ecol; i++ {
		cn, err = excelize.ColumnNumberToName(i)
		if err != nil {
			return
		}
		if len(tConf.Columns) > 0 {
			colName, ok = tConf.Columns[cn]
			if !ok {
				continue
			}
			buff.WriteString(colName)
			buff.WriteString(",")
			plen++
		} else {
			for j := srow; j <= erow; j++ {
				aixs = cn + strconv.Itoa(j)
				colName, ok = tConf.Cells[aixs]
				if ok {
					buff.WriteString(colName)
					buff.WriteString(",")
					plen++
				}
			}
		}
	}
	buff.Truncate(buff.Len() - 1)
	buff.WriteString(") values (")
	for i := 0; i < plen; i++ {
		buff.WriteString("?")
		if i < plen-1 {
			buff.WriteString(",")
		}
	}
	if !hasRowno {
		buff.WriteString(",")
		buff.WriteString("?")
	}
	buff.WriteString(")")
	sqlStr = buff.String()
	return
}
